﻿Public Class clsSzukanieWysylek

#Region "Attributes"

    Dim mvarWysylka As New DataTable
    Dim mvarWysylkaWedlugKryterium As New DataTable
    Dim mvarPozycjeWysylki As New DataTable
    Dim mvarPaczkiWysylki As New DataTable
    Dim mvarSumaIlosciWysylanych As Double
    Dim mvarCalkowitaIlosciWysylanych As Double

#End Region

#Region "Properties"

    Public Property WysylkaWedlugKryterium() As DataTable
        Get
            Return mvarWysylkaWedlugKryterium
        End Get
        Set(ByVal value As DataTable)
            mvarWysylkaWedlugKryterium = value
        End Set
    End Property

    Public Property Wysylka() As DataTable
        Get
            Return mvarWysylka
        End Get
        Set(ByVal value As DataTable)
            mvarWysylka = value
        End Set
    End Property

    Public Property PozycjeWysylki() As DataTable
        Get
            Return mvarPozycjeWysylki
        End Get
        Set(ByVal value As DataTable)
            mvarPozycjeWysylki = value
        End Set
    End Property

    Public Property PaczkiWysylki() As DataTable
        Get
            Return mvarPaczkiWysylki
        End Get
        Set(ByVal value As DataTable)
            mvarPaczkiWysylki = value
        End Set
    End Property

    Public Property SumaWysylanychIlosci() As Double
        Get
            Return mvarSumaIlosciWysylanych
        End Get
        Set(ByVal value As Double)
            mvarSumaIlosciWysylanych = value
        End Set
    End Property

    Public Property CalkowitaILoscWysylanych() As Double
        Get
            Return mvarCalkowitaIlosciWysylanych
        End Get
        Set(ByVal value As Double)
            mvarCalkowitaIlosciWysylanych = value
        End Set
    End Property

#End Region

#Region "Methods"

    Public Sub New()

        UtworzTabele()

    End Sub

    Private Sub UtworzTabele()

        With mvarWysylkaWedlugKryterium.Columns
            .Add("id_wysylki", GetType(Integer))
            .Add("numer_wysylki", GetType(String))
            .Add("id_zlecenia", GetType(Integer))
            .Add("numer", GetType(String))
            .Add("data", GetType(String))
            .Add("status", GetType(String))
            .Add("nazwa_skrocona", GetType(String))
        End With

        With mvarWysylka.Columns
            .Add("id_wysylki", GetType(Integer))
            .Add("data", GetType(String))
            .Add("id_pracownika", GetType(Integer))
        End With

        With mvarPozycjeWysylki.Columns
            .Add("id_pozycji_wysylki", GetType(Integer))
            .Add("id_pozycji_zlecenia", GetType(Integer))
            .Add("ilosc", GetType(Double))
            .Add("jednostka", GetType(String))
        End With

        With mvarPaczkiWysylki.Columns
            .Add("id_paczki_wysylki", GetType(Integer))
            .Add("waga", GetType(String))
            .Add("numer_listu_przewozowego", GetType(String))
        End With

    End Sub

    Public Sub ZliczCalkowitaIloscWyslanych(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal Zapytanie As Integer)

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 1, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT SUM(pozycje_wysylki.ilosc) AS suma FROM oem.pozycje_wysylki WHERE pozycje_zlecenia_id =" & lvarIdPozycjiZlecenia & ";", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie.ToString())
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    mvarCalkowitaIlosciWysylanych = CDbl(IIf(IsDBNull(lvarReader.Item("suma")), Nothing, lvarReader.Item("suma")))
                End While
            End If
        End If

    End Sub

    Public Sub ZliczWyslanaIlosc(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal IdPozycjiZlecenia As Integer, ByVal IdWysylki As Integer)

        Dim lvarIdPozycjiZlecenia As Integer
        Dim lvarIdWysylki As Integer

        lvarIdPozycjiZlecenia = IdPozycjiZlecenia
        lvarIdWysylki = IdWysylki

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 2, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT SUM(pozycje_wysylki.ilosc) AS suma FROM oem.pozycje_wysylki WHERE pozycje_zlecenia_id =" & lvarIdPozycjiZlecenia & " AND wysylka_id <> " & lvarIdWysylki & ";", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_id_pozycji_zlecenia", IdPozycjiZlecenia.ToString())
                .AddParameter("p_id_wysylki", IdWysylki.ToString())
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    mvarSumaIlosciWysylanych = CDbl(IIf(IsDBNull(lvarReader.Item("suma")), Nothing, lvarReader.Item("suma")))
                End While
            End If
        End If

    End Sub


    Public Sub SzukajWysylekWedlugKryterium(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal Zapytanie As String)

        Dim lvarNewRow As DataRow
        Dim lvarZapytanie As String

        lvarZapytanie = Zapytanie

        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 3, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT wysylka.id, wysylka.numer AS numer_wysylki, wysylka.zlecenie_id, zlecenie.numer,status_wysylki.nazwa, wysylka.data_wysylki FROM oem.wysylka, oem.zlecenie, oem.status_wysylki, oem.pracownik, oem.klient WHERE wysylka.zlecenie_id = zlecenie.id AND zlecenie.status_wysylki_id = status_wysylki.id AND wysylka.pracownik_id=pracownik.id AND zlecenie.klient_id = klient.id" & lvarZapytanie & " ORDER BY id DESC;", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie)
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarNewRow = mvarWysylkaWedlugKryterium.NewRow()
                    With lvarNewRow
                        .Item("id_wysylki") = IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))
                        .Item("numer_wysylki") = IIf(IsDBNull(lvarReader.Item("numer_wysylki")), Nothing, lvarReader.Item("numer_wysylki"))
                        .Item("id_zlecenia") = IIf(IsDBNull(lvarReader.Item("zlecenie_id")), Nothing, lvarReader.Item("zlecenie_id"))
                        .Item("numer") = IIf(IsDBNull(lvarReader.Item("numer")), Nothing, lvarReader.Item("numer"))
                        .Item("data") = IIf(IsDBNull(lvarReader.Item("data_wysylki")), Nothing, lvarReader.Item("data_wysylki"))
                        .Item("status") = IIf(IsDBNull(lvarReader.Item("nazwa")), Nothing, lvarReader.Item("nazwa"))
                        .Item("nazwa_skrocona") = IIf(IsDBNull(lvarReader.Item("nazwa_skrocona")), Nothing, lvarReader.Item("nazwa_skrocona"))
                    End With
                    mvarWysylkaWedlugKryterium.Rows.Add(lvarNewRow)
                End While
            End If
        End If

    End Sub


    Public Sub SzukajWysylek(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal Zapytanie As Integer)

        Dim lvarNewRow As DataRow
        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 4, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT wysylka.id, wysylka.data_wysylki, wysylka.pracownik_id FROM oem.wysylka WHERE zlecenie_id =" & lvarId & ";", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie.ToString())
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarNewRow = mvarWysylka.NewRow()
                    With lvarNewRow
                        .Item("id_wysylki") = IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))
                        .Item("data") = IIf(IsDBNull(lvarReader.Item("data_wysylki")), Nothing, lvarReader.Item("data_wysylki"))
                        .Item("id_pracownika") = IIf(IsDBNull(lvarReader.Item("pracownik_id")), Nothing, lvarReader.Item("pracownik_id"))
                    End With
                    mvarWysylka.Rows.Add(lvarNewRow)
                End While
            End If
        End If

    End Sub

    Public Sub SzukajWysylki(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal Zapytanie As Integer)

        Dim lvarNewRow As DataRow
        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 5, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT wysylka.id, wysylka.data_wysylki, wysylka.pracownik_id FROM oem.wysylka WHERE id =" & lvarId & ";", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie.ToString())
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarNewRow = mvarWysylka.NewRow()
                    With lvarNewRow
                        .Item("id_wysylki") = IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))
                        .Item("data") = IIf(IsDBNull(lvarReader.Item("data_wysylki")), Nothing, lvarReader.Item("data_wysylki"))
                        .Item("id_pracownika") = IIf(IsDBNull(lvarReader.Item("pracownik_id")), Nothing, lvarReader.Item("pracownik_id"))
                    End With
                    mvarWysylka.Rows.Add(lvarNewRow)
                End While
            End If
        End If

    End Sub


    Public Sub SzukajPozycjiWysylki(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal Zapytanie As Integer)

        Dim lvarNewRow As DataRow
        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 6, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT pozycje_wysylki.id, pozycje_wysylki.pozycje_zlecenia_id, pozycje_wysylki.ilosc, jednostka_miary.jednostka FROM oem.pozycje_wysylki, oem.jednostka_miary, oem.pozycje_zlecenia WHERE pozycje_zlecenia.jednostka_miary_id = jednostka_miary.id AND pozycje_wysylki.pozycje_zlecenia_id = pozycje_zlecenia.id  AND pozycje_wysylki.wysylka_id = " & lvarId & ";", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie.ToString())
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarNewRow = mvarPozycjeWysylki.NewRow()
                    With lvarNewRow
                        .Item("id_pozycji_wysylki") = IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))
                        .Item("id_pozycji_zlecenia") = IIf(IsDBNull(lvarReader.Item("pozycje_zlecenia_id")), Nothing, lvarReader.Item("pozycje_zlecenia_id"))
                        .Item("ilosc") = IIf(IsDBNull(lvarReader.Item("ilosc")), Nothing, lvarReader.Item("ilosc"))
                        .Item("jednostka") = IIf(IsDBNull(lvarReader.Item("jednostka")), Nothing, lvarReader.Item("jednostka"))
                    End With
                    mvarPozycjeWysylki.Rows.Add(lvarNewRow)
                End While
            End If
        End If

    End Sub

    Public Sub SzukajPaczekWysylki(ByVal lvarDbHolder As clsDataBase, ByRef ErrorHandler As clsErrorHandler, ByVal Zapytanie As Integer)

        Dim lvarNewRow As DataRow
        Dim lvarReader As OdbcDataReader = Nothing
        If lvarDbHolder.IsOpen Then
            Dim lvarSqlRunner As New clsSqlRunner(lvarDbHolder, "queries_application_oem", "cls_wysylka", 7, VAR_ERROR_HANDLER)
            '(lvarDbHolder, "SELECT * FROM oem.paczki_wysylki WHERE wysylka_id = " & lvarId & ";", VAR_ERROR_HANDLER)

            With lvarSqlRunner
                .AddParameter("p_zapytanie", Zapytanie.ToString())
                lvarReader = .ExecuteQuery
            End With
            If lvarReader.HasRows Then
                While lvarReader.Read
                    lvarNewRow = mvarPaczkiWysylki.NewRow()
                    With lvarNewRow
                        .Item("id_paczki_wysylki") = IIf(IsDBNull(lvarReader.Item("id")), Nothing, lvarReader.Item("id"))
                        .Item("waga") = IIf(IsDBNull(lvarReader.Item("waga")), Nothing, lvarReader.Item("waga"))
                        .Item("numer_listu_przewozowego") = IIf(IsDBNull(lvarReader.Item("numer_listu_przewozowego")), Nothing, lvarReader.Item("numer_listu_przewozowego"))
                    End With
                    mvarPaczkiWysylki.Rows.Add(lvarNewRow)
                End While
            End If
        End If

    End Sub

#End Region

End Class
